package de.wittekind.bundesjugendspiele.server.impl;

import java.io.IOException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import de.wittekind.bundesjugendspiele.server.DatabaseConnectionManager;
import de.wittekind.bundesjugendspiele.server.interfaces.Discipline;
import de.wittekind.bundesjugendspiele.server.interfaces.Riege;
import de.wittekind.bundesjugendspiele.server.interfaces.RiegenLoader;
import de.wittekind.bundesjugendspiele.server.interfaces.Schueler;
import de.wittekind.bundesjugendspiele.server.interfaces.Try;

/**
 * @author Sascha Wegener
 * 
 * @version 17.04.2013
 */
public class SQLRiegenLoader implements RiegenLoader {


	public SQLRiegenLoader() throws IOException {
		super();
	}

	@Override
	public Riege getRiege(int id) throws IOException {
		return new SQLRiege(id);
	}

	@Override
	public int getRiegenAmount() throws IOException {
		try {
			Connection c = DatabaseConnectionManager.getConnection();
			Statement s = c.createStatement();
			String sql = "select count(*) from riege";
			ResultSet rs = s.executeQuery(sql);
			rs.next();
			int amount = rs.getInt(1);
			s.close();
			return amount;
		} catch (SQLException ex) {
			throw new IOException(ex.getMessage());
		}
	}

	@Override
	public int newRiege() throws IOException {
		try {
			Connection c1 = DatabaseConnectionManager.getConnection();
			Statement s1 = c1.createStatement();
			String sql1 = "select max(id) from riege";
			ResultSet rs1 = s1.executeQuery(sql1);
			rs1.next();
			int newID = rs1.getInt(1) + 1;
			s1.close();
			Connection c = DatabaseConnectionManager.getConnection();
			Statement s = c.createStatement();
			String sql = String.format("insert into riege (id) values (%s)",
					newID);
			s.executeUpdate(sql);
			s.close();
			return newID;
		} catch (SQLException ex) {
			throw new IOException(ex.getMessage());
		}
	}

	@Override
	public void removeRiege(int id) throws IOException {
		try {
			Connection c = DatabaseConnectionManager.getConnection();
			Statement s = c.createStatement();
			String sql = "delete from riege where id=" + id;
			s.executeUpdate(sql);
			s.close();
		} catch (SQLException ex) {
			throw new IOException(ex.getMessage());
		}
	}

	@Override
	public Schueler[] getAllSchueler(int Jahrgang) throws IOException {
		try {
			Connection c = DatabaseConnectionManager.getConnection();
			Statement s = c.createStatement();
			String sql = "select id from schueler where jahr = " + Jahrgang;
			ResultSet rs = s.executeQuery(sql);
			List<Schueler> schueler = new ArrayList<Schueler>();
			while (rs.next()) {
				int id = rs.getInt(1);
				schueler.add(new SQLSchueler(id));
			}
			s.close();
			return schueler.toArray(new Schueler[] {});
		} catch (SQLException ex) {
			throw new IOException(ex.getMessage());
		}
	}

	@Deprecated
	@Override
	public Map<Schueler, Try> getLatestResults(int count, Discipline discipline) {
		return null;
	}

	@Deprecated
	@Override
	public Map<Schueler, Try> getBestResults(int count, Discipline discipline)
			throws IOException {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public Map<Schueler, Try> getLatestResults(int count,
			Discipline discipline, int year) throws IOException {
		try {
			Connection c = DatabaseConnectionManager.getConnection();
			Statement s = c.createStatement();
			String sql = String
					.format("select versuch.id,schueler.id from versuch, schueler where versuch.disziplin = %s and versuch.s_id = schueler.id and schueler.jahr=%s order by tswert desc limit %s",
							discipline, year, count);
			ResultSet rs = s.executeQuery(sql);
			Map<Schueler, Try> map = new HashMap<Schueler, Try>();
			while (rs.next()) {
				int sid = rs.getInt(2);
				int vid = rs.getInt(1);
				map.put(new SQLSchueler(sid), new SQLTry(vid));
			}
			s.close();
			return map;
		} catch (SQLException ex) {
			throw new IOException(ex.getMessage());
		}
	}

	@Override
	public Map<Schueler, Try> getBestResults(int count, Discipline discipline,
			int year) throws IOException {
		try {
			Connection c = DatabaseConnectionManager.getConnection();
			Statement s = c.createStatement();
			String sql = null;
			if (Discipline.AUSDAUER.hasDiscipline(discipline)
					|| Discipline.SPRINT.hasDiscipline(discipline)) {
				sql = String
						.format("select versuch.id,schueler.id from versuch, schueler where versuch.disziplin = %s and versuch.s_id = schueler.id and schueler.jahr=%s order by wert asc limit %s",
								discipline.ordinal(), year, count);
			} else {
				sql = String
						.format("select versuch.id,schueler.id from versuch, schueler where versuch.disziplin = %s and versuch.s_id = schueler.id and schueler.jahr=%s order by wert desc limit %s",
								discipline.ordinal(), year, count);
			}
			ResultSet rs = s.executeQuery(sql);
			Map<Schueler, Try> map = new HashMap<Schueler, Try>();
			while (rs.next()) {
				int sid = rs.getInt(2);
				int vid = rs.getInt(1);
				map.put(new SQLSchueler(sid), new SQLTry(vid));
			}
			s.close();
			return map;
		} catch (SQLException ex) {
			throw new IOException(ex.getMessage());
		}
	}

	public int getMinJahrgang() throws IOException {
		try {
			Connection c = DatabaseConnectionManager.getConnection();
			Statement s = c.createStatement();
			String sql = "select min(jahr) from schueler";
			ResultSet rs = s.executeQuery(sql);
			rs.next();
			int amount = rs.getInt(1);
			s.close();
			return amount;
		} catch (SQLException ex) {
			throw new IOException(ex.getMessage());
		}
	}

	public int getMaxJahrgang() throws IOException {
		try {
			Connection c = DatabaseConnectionManager.getConnection();
			Statement s = c.createStatement();
			String sql = "select max(jahr) from schueler";
			ResultSet rs = s.executeQuery(sql);
			rs.next();
			int amount = rs.getInt(1);
			s.close();
			return amount;
		} catch (SQLException ex) {
			throw new IOException(ex.getMessage());
		}
	}

	@Override
	public ArrayList<Integer> getSiegerurkunden() throws IOException {
		/*
		ArrayList <Integer> siegerurkunden = new ArrayList<Integer>();
		int amount = getRiegenAmount();
		Riege currentRiege;
		for(int i=0; i<amount; i++){
			currentRiege = getRiege(i+1);
			int ids[] = currentRiege.getSchuelerIDs();
			for(int p=0; p<ids.length; p++){
			}
		}*/
		return null;
		
	}

	@Override
	public ArrayList<Integer> getEhrenurkunden() throws IOException {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public int[] getRiegenIDs() throws IOException {
		// TODO Auto-generated method stub
		return null;
	}
}
